/* ******************************************************************************************************* */
/* ************************ Constructs a dataset for baseline fund-level analysis  *************************/
/* ******************************************************************************************************* */

**** For contentious proposals;
data test; set temp.iss_votes_ret;
  if issForMgmt = 1 then iss_win = mgmt_win;
  if issForMgmt = 0 then iss_win = -mgmt_win;
  month = month(meetingdate);
  yyyymm = year(meetingdate)*100+month;

  if 0 < abs(margin) <= .20 and issue ne ''; 
  * temp.iss_votes_ret is from 08ISS_Votes_CRSPMF.sas;

proc sql;
  create table test as
  select a.*, b.*
  from test as a left join temp.meetings_car_permno 
    (keep=permno meetingdate prc_pr2 cfacshr_pr2 car_pr10d_m car5d_m car7d_m car12d_m) as b
  on a.permno = b.permno and year(a.meetingdate) = year(b.meetingdate) and month(a.meetingdate) = month(b.meetingdate) and day(a.meetingdate) = day(b.meetingdate); * intck('day', a.meetingdate, b.meetingdate) = 0;
quit;

data test; set test;
  if cfacshr_pr2 ne 0 then market_value_pr2 = prc_pr2*nbr_shares*(cfacshr_rdate/cfacshr_pr2);
  if market_value_pr2 > 0;
run;  

proc sort data = test; by year permno meetingdate wficn crsp_cl_grp itemonagendaid; 

data test; set test; by year permno meetingdate;
  if WinVote = 1 then car12d_m_win = car12d_m;
  if WinVote = 0 then car12d_m_win = -car12d_m;
  if WinVote = 1 then car5d_m_win = car5d_m;
  if WinVote = 0 then car5d_m_win = -car5d_m;
  if WinVote = 1 then car7d_m_win = car7d_m;
  if WinVote = 0 then car7d_m_win = -car7d_m;
run;

proc sort data = test; by wficn crsp_cl_grp year permno meetingdate;

proc means data = test noprint; by wficn crsp_cl_grp year permno meetingdate;
  var WinVote;
  output out = num (drop=_type_ _freq_) n = number_votes;

data test1; merge test num; by wficn crsp_cl_grp year permno meetingdate;
  market_value_adj = market_value_pr2/number_votes;
  pwt_adj = pwt/number_votes;
  wt_adj = wt/number_votes;
  In_minority = 1-WinVote;

proc sort data = test1; by wficn crsp_cl_grp year issAgendaItemId; 
run;

proc means data = test1 noprint; by wficn crsp_cl_grp year;
  var WinVote; where car12d_m ne .;
  output out = count (drop=_type_ _freq_) n = num_votes mean(pwt) = pwt mean(wt) = wt; run;

/**** value weighted ****/
proc means data = test1 noprint; by wficn crsp_cl_grp year; 
  var car12d_m_win car7d_m_win car5d_m_win; weight market_value_adj; 
  output out = win_ret1a (drop=_type_ _freq_) 
    mean = vote_alpha vote_alpha7 vote_alpha5;

/**** equal weighted ****/
proc means data = test1 noprint; by wficn crsp_cl_grp year; 
  var car12d_m_win;
  output out = win_ret1b (drop=_type_ _freq_) mean = vote_alpha_ew;

proc means data = test1 noprint; by wficn crsp_cl_grp year; where fundfor = 1;
  var car12d_m_win; weight market_value_adj; 
  output out = win_ret1c (drop=_type_ _freq_) mean = vote_alpha_for;

proc means data = test1 noprint; by wficn crsp_cl_grp year; where fundfor = 0;
  var car12d_m_win; weight market_value_adj; 
  output out = win_ret1d (drop=_type_ _freq_) mean = vote_alpha_against;

data coeff5;  merge win_ret1a win_ret1b win_ret1c win_ret1d count; by wficn crsp_cl_grp year;

proc sort data = coeff5 nodupkey; by wficn crsp_cl_grp year; 
run;


**** Lagged vote alpha;
proc sql;
  create table prior_1yr as
  select a.wficn, a.crsp_cl_grp, a.year, b.*
  from coeff5 as a left join test1 as b
  on a.wficn = b.wficn and a.crsp_cl_grp = b.crsp_cl_grp and a.year-b.year in (1); 
quit;

proc sort data = prior_1yr nodupkey; by wficn crsp_cl_grp year issAgendaItemId permno meetingdate itemonagendaid; run;

proc means data = prior_1yr noprint; by wficn crsp_cl_grp year; 
  var WinVote; where car12d_m ne .;
  output out = count_ (drop=_type_ _freq_) n = num_pr1yr;

proc means data = prior_1yr noprint; by wficn crsp_cl_grp year; 
  var car12d_m_win car7d_m_win car5d_m_win; weight market_value_adj; where market_value_adj ne .;
  output out = win_ret_1a (drop=_type_ _freq_) 
    mean = vote_alpha_lag vote_alpha7_lag vote_alpha5_lag;

proc means data = prior_1yr noprint; by wficn crsp_cl_grp year; 
  var car12d_m_win;
  output out = win_ret_1b (drop=_type_ _freq_) 
    mean = vote_alpha_lag_ew;

data coeff_pr1yr; merge win_ret_1a win_ret_1b count_; by wficn crsp_cl_grp year;
run;

proc sort data = coeff5 nodupkey; by wficn crsp_cl_grp year; 

data coeff5x; merge coeff5 coeff_pr1yr; by wficn crsp_cl_grp year;

data fundinfo; set temp.fundinfo;
  if mtna ne .; 

proc sql;
  create table coeff5z as
  select a.*, b.*
  from coeff5x as a left join fundinfo as b
  on a.wficn = b.wficn and a.crsp_cl_grp = b.crsp_cl_grp and a.year = year(b.caldt)+1 and month(b.caldt) = 12; 
quit;

proc sort; by wficn crsp_cl_grp year caldt;

data coeff5z; set coeff5z; by wficn crsp_cl_grp year;
  if last.year;
  if wficn = 2004443 and turnover > 257 then turnover = turnover/100;
  if wficn = 2004579 and turnover > 110120 then turnover = 0.68;
  if wficn = 2024613 and turnover > 800 then turnover = turnover/100;
run;



*** Past and current active voting behavior;
proc sort data = test1; by wficn crsp_cl_grp year;

proc means noprint data = test1; by wficn crsp_cl_grp year;
  var voteAgainstMgmt iss_conform voteAgainstMgmt_ISS_conform voteAgainstMgmt_non_conform In_minority; where voteAgainstMgmt ne . and iss_conform ne .;
  output out = iss_mgmt1a (drop=_type_ _freq_) mean = voteAgainstMgmt_contentious iss_conform_contentious voteAgainstMgmt_conform_con voteAgainstMgmt_nconform_con In_minority_con;

*** Past voting in the minority on non-contentious proposals;
data non_con; set temp.iss_votes_ret;
  if issForMgmt = 1 then iss_win = mgmt_win;
  if issForMgmt = 0 then iss_win = -mgmt_win;
  month = month(meetingdate);
  yyyymm = year(meetingdate)*100+month;
  In_minority_non_con = 1-WinVote;
  if abs(margin) > .20 and issue ne '';
  keep wficn crsp_cl_grp year permno meetingdate issue issAgendaItemId itemonagendaid margin voteAgainstMgmt voteAgainstMgmt_ISS_conform voteAgainstMgmt_non_conform iss_conform WinVote nbr_shares cfacshr_rdate IN_MINORITY_NON_CON;

proc sql;
  create table non_con as
  select a.*, b.*
  from non_con as a, temp.meetings_car_permno 
    (keep=permno meetingdate prc_pr2 cfacshr_pr2 car_pr10d_m car5d_m car7d_m car12d_m) as b
  where a.permno = b.permno and year(a.meetingdate) = year(b.meetingdate) and month(a.meetingdate) = month(b.meetingdate) and day(a.meetingdate) = day(b.meetingdate) and prc_pr2*nbr_shares*(cfacshr_rdate/cfacshr_pr2) > 0; 
quit;

data non_con; set non_con;
  market_value_pr2 = prc_pr2*nbr_shares*(cfacshr_rdate/cfacshr_pr2);
  if market_value_pr2 > 0;

proc sort data = non_con; by wficn crsp_cl_grp year;

proc means noprint data = non_con; by wficn crsp_cl_grp year;
  var In_minority_non_con voteAgainstMgmt iss_conform voteAgainstMgmt_ISS_conform voteAgainstMgmt_non_conform;
  output out = iss_mgmt1b (drop=_type_ _freq_) mean = In_minority_non_con voteAgainstMgmt_non_con iss_conform_non_con voteAgainstMgmt_conform_ncon voteAgainstMgmt_nconform_ncon;

data all_proposals; set non_con test1;

proc sort data = all_proposals; by wficn crsp_cl_grp year;

proc means noprint data = all_proposals; by wficn crsp_cl_grp year;
  var voteAgainstMgmt iss_conform voteAgainstMgmt_ISS_conform voteAgainstMgmt_non_conform;
  output out = all_proposals2 (drop=_type_ _freq_) mean = voteAgainstMgmt_all iss_conform_all voteAgainstMgmt_conform_all voteAgainstMgmt_nconform_all;
run; 

data iss_mgmt; merge iss_mgmt1a iss_mgmt1b all_proposals2; by wficn crsp_cl_grp year;

proc means n mean median p25 p75 min max;
  var voteAgainstMgmt_conform_all voteAgainstMgmt_nconform_all;
run;


****************************************************************************************************;
*************************** Deviate from family concensus: ALl proposals ***************************;
****************************************************************************************************;
proc sql;
  create table all_proposals as
  select a.*, b.mgmt_no2, b.caldt
  from all_proposals (keep=wficn crsp_cl_grp year permno meetingdate itemonagendaid voteAgainstMgmt market_value_pr2) as a left join fundinfo as b
  on a.wficn = b.wficn and a.crsp_cl_grp = b.crsp_cl_grp and a.year = year(b.caldt) and month(b.caldt) = 12; 
quit;

proc sort data = all_proposals; by mgmt_no2 year permno meetingdate itemonagendaid wficn crsp_cl_grp caldt;

data all_proposals; set all_proposals; by mgmt_no2 year permno meetingdate itemonagendaid wficn crsp_cl_grp;
  if last.crsp_cl_grp;

proc means noprint data = all_proposals; by mgmt_no2 year permno meetingdate itemonagendaid;
  var voteAgainstMgmt; where mgmt_no2 ne '';
  output out = deviate_all (drop=_type_ _freq_) mode = voteAgainstMgmt_family n = num_funds;
run; 

data all_proposals (compress=yes); merge all_proposals deviate_all (in=a); by mgmt_no2 year permno meetingdate itemonagendaid;
  if a;
  if num_funds = 1 then do; sole_voter = 1; sole_or_independent = 1; end; 
  if num_funds > 1 then do;
    sole_voter = 0;
    if voteAgainstMgmt_family ne voteAgainstMgmt then do; independent_from_family = 1; sole_or_independent = 1; end;
	else do; independent_from_family = 0; sole_or_independent = 0; end;
  end;
  keep wficn crsp_cl_grp year independent_from_family sole_voter sole_or_independent;

proc sort data = all_proposals; by wficn crsp_cl_grp year;

proc means noprint data = all_proposals; by wficn crsp_cl_grp year;
  var independent_from_family sole_voter sole_or_independent; 
  output out = deviate_family_all (drop=_type_ _freq_) mean = independent_from_family_all sole_voter_all sole_or_independent_all;
run; 

****************************************************************************************************;
******************** END OF Computing Deviate from family concensus: ALl proposals *****************;
****************************************************************************************************;


proc sql;
  create table informed as
  select a.*, b.*
  from coeff5z as a left join iss_mgmt as b
  on a.wficn = b.wficn and a.crsp_cl_grp = b.crsp_cl_grp and a.year = b.year+1;
quit;

proc sql;
  create table informed as
  select a.*, b.voteagainstmgmt_contentious as voteagainstmgmt_contentious_t, b.iss_conform_contentious as iss_conform_contentious_t, b.iss_conform_all as iss_conform_all_t, b.in_minority_non_con as in_minority_non_con_t, b.In_minority_con as In_minority_con_t
  from informed as a left join iss_mgmt as b
  on a.wficn = b.wficn and a.crsp_cl_grp = b.crsp_cl_grp and a.year = b.year;
quit;

proc sql;
  create table informed as
  select a.*, b.sole_voter_all, b.independent_from_family_all, b.sole_or_independent_all
  from informed as a left join deviate_family_all as b
  on a.wficn = b.wficn and a.crsp_cl_grp = b.crsp_cl_grp and a.year = b.year+1;
quit;

proc sql;
  create table informed as
  select a.*, b.flow_style_adj as flow_style_adj_pr12, b.flow_style_adj_std, b.flow_std, b.flow_comp
  from informed as a left join temp.flow_annual as b
  on a.wficn = b.wficn and a.crsp_cl_grp = b.crsp_cl_grp and a.year = b.year+1; * temp.flow_annual is from 02CRSPMF_Flows.sas;
quit;

proc sql;
  create table informed as
  select a.*, b.flow_style_adj as flow_style_adj_t
  from informed as a left join temp.flow_annual as b
  on a.wficn = b.wficn and a.crsp_cl_grp = b.crsp_cl_grp and a.year = b.year;
quit;

proc sql;
  create table informed as
  select a.*, b.mret as mret_pr12, b.mret_style_adj as mret_style_adj_pr12, b.mret_std, b.ret_comp, b.mret_style_adj_std
  from informed as a left join temp.fund_abnret_annual as b
  on a.wficn = b.wficn and a.crsp_cl_grp = b.crsp_cl_grp and a.year = b.year+1;
quit; 

proc sql;
  create table informed as
  select a.*, b.mret as mret_t, b.mret_style_adj as mret_style_adj_t, b.num_months
  from informed as a left join temp.fund_abnret_annual as b
  on a.wficn = b.wficn and a.crsp_cl_grp = b.crsp_cl_grp and a.year = b.year;
quit; 
************************************* temp.fundret_annual is from 03CRSPMF.sas ********************************;


data informed; set informed; 
  if wficn = . and crsp_cl_grp ne . then wficn = crsp_cl_grp*10000;

proc sort data = informed; by year crsp_obj_cd2 wficn descending num_votes;

proc rank data = informed out = informed1 groups = 5; by year; 
  where (index(crsp_obj_cd2, 'ED') = 1) and mtna >= 5 and num_votes > 10 and mtna ne . and family_tna ne . and institutional_fund ne . and index_fund2 ne . and mret_style_adj_pr12 ne . and flow_style_adj_pr12 ne . 
    and num_stocks ne .  and turnover > 0 and expenses2 ne . and frnt_ld2+rear_ld2 ne . and voteagainstmgmt_all ne . and iss_conform_all ne . and sole_or_independent_all ne . and size_score ne . and flow_style_adj_std ne . and mret_style_adj_std ne . and vote_alpha_lag ne .;
  var vote_alpha vote_alpha_lag vote_alpha5 vote_alpha5_lag vote_alpha7 vote_alpha7_lag vote_alpha_ew vote_alpha_lag_ew ; 
  ranks q_vote_alpha q_vote_alpha_lag q_vote_alpha5 q_vote_alpha5_lag q_vote_alpha7 q_vote_alpha7_lag q_vote_alpha_ew q_vote_alpha_lag_ew;

proc rank data = informed1 out = informed1 groups = 3; by year;
  var vote_alpha vote_alpha_lag;
  ranks t_vote_alpha t_vote_alpha_lag;

proc rank data = informed1 out = informed1 groups = 4; by year;
  var vote_alpha vote_alpha_lag;
  ranks q4_vote_alpha q4_vote_alpha_lag;

proc rank data = informed1 out = informed1 groups = 10; by year;
  var vote_alpha vote_alpha_lag;
  ranks d_vote_alpha d_vote_alpha_lag;

proc rank data = informed1 out = informed1 groups = 20; by year;
  var vote_alpha vote_alpha_lag;
  ranks v_vote_alpha v_vote_alpha_lag;

data informed1; set informed1; by year crsp_obj_cd2; 
  total_loads = frnt_ld+rear_ld;
  total_loads2 = frnt_ld2+rear_ld2;

  if q_vote_alpha = 4 then informed = 1; else if q_vote_alpha ne . then informed = 0;
  if q_vote_alpha_lag = 4 then informed_lag = 1; else if q_vote_alpha_lag ne . then informed_lag = 0;

  if q_vote_alpha = 0 then bottomQ = 1; else if q_vote_alpha ne . then bottomQ = 0;
  if q_vote_alpha_lag = 0 then bottomQ_lag = 1; else if q_vote_alpha_lag ne . then bottomQ_lag = 0;

  if T_vote_alpha in (2) then topT = 1; else if T_vote_alpha ne . then topT = 0;
  if T_vote_alpha_lag in (2) then topT_lag = 1; else if T_vote_alpha_lag ne . then topT_lag = 0;

  if T_vote_alpha in (0) then bottomT = 1; else if T_vote_alpha ne . then bottomT = 0;
  if T_vote_alpha_lag in (0) then bottomT_lag = 1; else if T_vote_alpha_lag ne . then bottomT_lag = 0;

  if d_vote_alpha in (9) then topD = 1; else if d_vote_alpha ne . then topD = 0;
  if d_vote_alpha_lag in (9) then topD_lag = 1; else if d_vote_alpha_lag ne . then topD_lag = 0;

  if d_vote_alpha in (0) then bottomD = 1; else if d_vote_alpha ne . then bottomD = 0;
  if d_vote_alpha_lag in (0) then bottomD_lag = 1; else if d_vote_alpha_lag ne . then bottomD_lag = 0;

  if q4_vote_alpha = 3 then informedQ4 = 1; else if q4_vote_alpha ne . then informedQ4 = 0;
  if q4_vote_alpha_lag = 3 then informedQ4_lag = 1; else if q4_vote_alpha_lag ne . then informedQ4_lag = 0;

  if q4_vote_alpha = 0 then bottomQ4 = 1; else if q4_vote_alpha ne . then bottomQ4 = 0;
  if q4_vote_alpha_lag = 0 then bottomQ4_lag = 1; else if q4_vote_alpha_lag ne . then bottomQ4_lag = 0;

  if v_vote_alpha in (19) then topV = 1; else if v_vote_alpha ne . then topV = 0;
  if v_vote_alpha_lag in (19) then topV_lag = 1; else if v_vote_alpha_lag ne . then topV_lag = 0;

  if v_vote_alpha in (0) then bottomV = 1; else if v_vote_alpha ne . then bottomV = 0;
  if v_vote_alpha_lag in (0) then bottomV_lag = 1; else if v_vote_alpha_lag ne . then bottomV_lag = 0;

  if q_vote_alpha7 = 4 then informed7 = 1; else if q_vote_alpha7 ne . then informed7 = 0;
  if q_vote_alpha7_lag = 4 then informed7_lag = 1; else if q_vote_alpha7_lag ne . then informed7_lag = 0;

  if q_vote_alpha5 = 4 then informed5 = 1; else if q_vote_alpha5 ne . then informed5 = 0;
  if q_vote_alpha5_lag = 4 then informed5_lag = 1; else if q_vote_alpha5_lag ne . then informed5_lag = 0;

  if q_vote_alpha_ew = 4 then informed_ew = 1; else if q_vote_alpha_ew ne . then informed_ew = 0;
  if q_vote_alpha_lag_ew = 4 then informed_lag_ew = 1; else if q_vote_alpha_lag_ew ne . then informed_lag_ew = 0;

proc sort data = informed1 nodupkey; by wficn year; run;

data temp.informed_fund_level; set informed1;
  vote_alpha = vote_alpha*100;
  vote_alpha_lag = vote_alpha_lag*100;

  vote_alpha_ew = vote_alpha_ew*100;
  vote_alpha_lag_ew = vote_alpha_lag_ew*100;

  size = log(mtna);
  fsize = log(family_tna);
  log_num_stocks = log(num_stocks);
  log_num_funds = log(num_funds);
  log_fund_age = log(fund_age);
  crsp_obj_cd_year = crsp_obj_cd2||year;
  if 1 <= rank_nfunds_msa <= 2 then top_city = 1; else top_city = 0;
  if mgmt_no2 in ('BLK', 'SSB', 'VAN') then big3 = 1; else big3 = 0;
  if wficn = . and crsp_cl_grp ne . then wficn = crsp_cl_grp*10000;
  if turnover > 0 then logturnover = log(turnover);
run;


proc export data= temp.informed_fund_level 
            outfile= "D:\Dropbox\InformedVoting\informed_fund_level.dta" 
            dbms=stata replace;
run;



data bins; set temp.informed_fund_level;
  vote_alpha_lag_bin = int(vote_alpha_lag*2)/2; 
  vote_alpha_lag_bin = vote_alpha_lag_bin/100;
  vote_alpha = vote_alpha/100;

proc sort; by vote_alpha_lag_bin;

proc means noprint; by vote_alpha_lag_bin;
  var vote_alpha ;
  output out = bins_plot (drop=_type_ _freq_) n = num_funds mean = vote_alpha sum(mtna) = mtna sum(num_votes) = num_votes; 
run;

proc export data= bins_plot 
            outfile= "D:\Dropbox\InformedVoting\bins_plot.dta" 
            dbms=stata replace;
run;

